package common;

import java.util.ArrayList;
import ConnDatabase.ConnDatabase;
import java.sql.*;

public class HiredRoomList {
	
	public HiredRoomList() {
	
	}

	public SelectedPart getRooms(int pageIndex,int pageSize,
				String sectionPara,String buildingPara,
				String roomType,String area) throws RoomException {
		
		String sectionStr = "";
		String buildStr = "";
		String roomTypeStr = "" ;
		String areaStr = "" ;
		if (!sectionPara.equalsIgnoreCase(""))
			sectionStr = " and r.seccode='" + sectionPara + "'";
		if (!buildingPara.equals(""))
			buildStr = " and r.building='" + buildingPara + "'";
		if (!roomType.equals(""))
			roomTypeStr = " and r.room_type='" + roomType + "' " ;
		if (!area.equals(""))
			if (area.equals("50-"))
				areaStr = " and r.area<50 ";
			else if (area.equals("50+100-"))
				areaStr = " and r.area<100 and r.area>50 " ;
			else if (area.equals("100+150-"))
				areaStr = " and r.area<150 and r.area>150 " ;
			else if (area.equals("150+"))
				areaStr = " and r.area>150 ";						
			
		String countStr = "select count(*) from "; 
		countStr += " room r,section s,customer c,hire_in h";
		countStr += " where r.seccode = s.serialno and ";
		countStr += " c.serialno=h.customer and h.active=1";
		countStr += " and r.seccode=h.section and r.building=h.building ";
		countStr += " and r.room_code= h.room ";
		countStr += sectionStr + buildStr + roomTypeStr + areaStr;
		countStr += " and r.saleState=9";
		
		String roomsStr = "select r.code r_id,h.id h_id,s.serialno section,s.name sectionname,r.building,r.room_code room,";
		roomsStr += "r.area,r.room_type roomtype,c.signatory customers from ";
		roomsStr += " room r, section s,customer c,hire_in h";
		roomsStr += " where r.seccode = s.serialno and "; 
		roomsStr += " c.serialno=h.customer and h.active=1";
		roomsStr += " and r.seccode=h.section and r.building=h.building ";
		roomsStr += " and r.room_code= h.room ";
		roomsStr += sectionStr + buildStr + roomTypeStr + areaStr;
		roomsStr += " and r.saleState=9";
		
		SelectedPart sp = null;
						
		int factCount = 0;
		int skipRooms = (pageIndex - 1) * pageSize;
		int pageSize2 = pageSize;
		System.out.println("pageIndex : " + pageIndex);
		int allRooms = 0;
		ArrayList al = new ArrayList();
		
		ResultSet roomRS = null;
		
		try {
			System.out.println("-----begin connection");
			ConnDatabase conn = new ConnDatabase();
			System.out.println("----already connect");
			roomRS = conn.executeQuery(countStr);
			if (roomRS.next())
				allRooms = roomRS.getInt(1);
			System.out.println("allrooms : " + allRooms);	
			roomRS = conn.executeQuery(roomsStr);
			System.out.println("skiprooms : " + skipRooms);
			while (skipRooms -- > 0 && roomRS.next()) ;
			
			System.out.println("pageSize : " + pageSize);
			while (pageSize2-- > 0 && roomRS.next()) {
				factCount ++;
				int r_id = roomRS.getInt("r_id");
				int h_id = roomRS.getInt("h_id");
				System.out.println("id : " + h_id);
				String section = roomRS.getString("section").trim();
				String sectionName = roomRS.getString("sectionname").trim();
				String building = roomRS.getString("building").trim();
				String room = roomRS.getString("room").trim();
				float jzArea = roomRS.getFloat("area");
				String roomType2 = roomRS.getString("roomtype").trim();
				String customers = roomRS.getString("customers").trim();
				
				HiredRoomDetail hrd = new HiredRoomDetail(r_id,h_id,section,
								sectionName,building,room,roomType2,
								jzArea,customers);
				al.add(hrd);	
				System.out.println("------" + section);			
			}
		} catch (SQLException se) {
			System.out.println("RoomList : " + se.getMessage());
			throw new RoomException("sqlexception while get hirableroom : " +
							se.getMessage());
		} finally {
			try {
				if (roomRS != null)
					roomRS.close();
			} catch (Exception e) {
				System.out.print("can't close roomRS in HirableRoomList");
			}	
		}	
		// get selected part
		sp = new SelectedPart(allRooms,pageIndex,pageSize,
							factCount,al);
		return sp;								
			
	}	
	
}